package com.cn.parks.dao.impl;

import com.cn.parks.dao.ParkingSeatDao;
import com.cn.parks.entity.ParkingSeat;
import com.cn.parks.utils.DBUtil;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 停车位数据访问实现类
 * 实现对停车位表的增删改查等基本操作
 */
public class ParkingSeatDaoImpl implements ParkingSeatDao {

    /**
     * 添加停车位
     * @param parkingSeat 停车位对象
     * @return 影响的行数，1表示添加成功，0表示添加失败
     * @throws Exception SQL执行异常
     */
    @Override
    public int insert(ParkingSeat parkingSeat) throws Exception {
        String sql = "INSERT INTO parking_seat (seat_num, seat_section, seat_state, seat_tag) VALUES (?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, parkingSeat.getSeatNum());
            pstmt.setString(2, parkingSeat.getSeatSection());
            pstmt.setInt(3, parkingSeat.getSeatState());
            pstmt.setString(4, parkingSeat.getSeatTag());
            
            return pstmt.executeUpdate();
        }
    }

    /**
     * 根据ID删除停车位
     * @param seatId 停车位ID
     * @return 影响的行数，1表示删除成功，0表示删除失败
     * @throws Exception SQL执行异常
     */
    @Override
    public int deleteById(String seatId) throws Exception {
        String sql = "DELETE FROM parking_seat WHERE seat_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, seatId);
            return pstmt.executeUpdate();
        }
    }

    /**
     * 更新停车位信息
     * @param parkingSeat 停车位对象
     * @return 影响的行数，1表示更新成功，0表示更新失败
     * @throws Exception SQL执行异常
     */
    @Override
    public int update(ParkingSeat parkingSeat) throws Exception {
        String sql = "UPDATE parking_seat SET seat_num=?, seat_section=?, seat_state=?, seat_tag=? WHERE seat_id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, parkingSeat.getSeatNum());
            pstmt.setString(2, parkingSeat.getSeatSection());
            pstmt.setInt(3, parkingSeat.getSeatState());
            pstmt.setString(4, parkingSeat.getSeatTag());
            pstmt.setString(5, parkingSeat.getSeatId());
            
            return pstmt.executeUpdate();
        }
    }

    /**
     * 根据ID查询停车位
     * @param seatId 停车位ID
     * @return 停车位对象，如果不存在返回null
     * @throws Exception SQL执行异常
     */
    @Override
    public ParkingSeat getById(String seatId) throws Exception {
        String sql = "SELECT * FROM parking_seat WHERE seat_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, seatId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return extractParkingSeat(rs);
                }
            }
        }
        return null;
    }

    @Override
    public Integer getByCarId(String seatNum) throws Exception {
        String sql = "SELECT car_id FROM parking_seat WHERE seat_num = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, seatNum);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("car_id");
                }
            }
        }
        return 0;
    }

    /**
     * 根据车位号查询停车位
     * @param seatNum 车位号
     * @return 停车位对象，如果不存在返回null
     * @throws Exception SQL执行异常
     */
    @Override
    public Integer getBySeatNum(String seatNum) throws Exception {
        String sql = "SELECT seat_state FROM parking_seat WHERE seat_num = ?";
        Connection conn = DBUtil.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, seatNum);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    return rs.getInt("seat_state");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据车位号查询车位标签
     * @param seatNum 车位号
     * @return 车位标签，如果不存在返回""
     * @throws Exception SQL执行异常
     */
    @Override
    public String getSeatTag(String seatNum) throws Exception {
        String sql = "SELECT seat_tag FROM parking_seat WHERE seat_num = ?";
        Connection conn = DBUtil.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, seatNum);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getString("seat_tag");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return "";
    }

    /**
     * 更新停车位状态
     * @param seatNum 车位号
     * @throws Exception SQL执行异常
     */
    @Override
    public void updateSeatState(String seatNum, Integer carId, String carNum, Integer userId) throws Exception {
        String sql = "UPDATE parking_seat SET seat_state=1 , car_id = ? , car_number = ? , user_id = ? WHERE seat_num=?";
        Connection conn = DBUtil.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, carId);
            pstmt.setString(2, carNum);
            pstmt.setInt(3, userId);
            pstmt.setString(4, seatNum);
            pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 查询所有停车位
     * @return 停车位列表
     * @throws Exception SQL执行异常
     */
    @Override
    public List<ParkingSeat> getAll() throws Exception {
        String sql = "SELECT * FROM parking_seat ORDER BY seat_num";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            List<ParkingSeat> list = new ArrayList<>();
            while (rs.next()) {
                list.add(extractParkingSeat(rs));
            }
            return list;
        }
    }

    /**
     * 根据区域查询停车位
     * @param section 区域
     * @return 停车位列表
     * @throws Exception SQL执行异常
     */
    @Override
    public List<ParkingSeat> getBySection(String section) throws Exception {
        String sql = "SELECT * FROM parking_seat WHERE seat_section LIKE ? ORDER BY seat_num";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, "%" + section + "%");
            try (ResultSet rs = pstmt.executeQuery()) {
                List<ParkingSeat> list = new ArrayList<>();
                while (rs.next()) {
                    list.add(extractParkingSeat(rs));
                }
                return list;
            }
        }
    }

    /**
     * 查询空闲的停车位
     * @return 空闲停车位列表
     * @throws Exception SQL执行异常
     */
    @Override
    public List<ParkingSeat> getAvailableSeats() throws Exception {
        String sql = "SELECT * FROM parking_seat WHERE seat_state = 0 ORDER BY seat_num";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            List<ParkingSeat> list = new ArrayList<>();
            while (rs.next()) {
                list.add(extractParkingSeat(rs));
            }
            return list;
        }
    }

    /**
     * 更新停车位状态
     * @param seatId 停车位ID
     * @param state 新状态
     * @return 影响的行数，1表示更新成功，0表示更新失败
     * @throws Exception SQL执行异常
     */
    @Override
    public int updateState(String seatId, Integer state) throws Exception {
        String sql = "UPDATE parking_seat SET seat_state = ? WHERE seat_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, state);
            pstmt.setString(2, seatId);
            return pstmt.executeUpdate();
        }
    }
    /**
     * 根据CarId更新停车位状态
     * @param carId 车ID
     * @throws Exception SQL执行异常
     */
    @Override
    public void updateSeatStateByCarId(Integer carId) throws Exception {
        String sql = "UPDATE parking_seat SET seat_state = 0 , car_id = null, car_number = null, user_id = null WHERE car_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, carId);
            pstmt.executeUpdate();
        }
    }
    /*
     * 查询临时空闲车位
     */
    @Override
    public List<ParkingSeat> getTempAvailableSeats() throws Exception {
        String sql = "SELECT * FROM parking_seat WHERE seat_tag = '临时车位' AND seat_state = 0 ORDER BY seat_num";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            List<ParkingSeat> list = new ArrayList<>();
            while (rs.next()) {
                list.add(extractParkingSeat(rs));
            }
            return list;
        }
    }

    /**
     * 获取停车位总数
     * @return 停车位总数
     * @throws Exception SQL执行异常
     */
    @Override
    public Integer getParkCount() throws Exception {
        String sql = "SELECT COUNT(*) FROM parking_seat";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            if (rs.next()) {
                return rs.getInt(1);
            }
        }
        return 0;
    }

    /**
     * 获取空闲停车位总数
     * @return 空闲停车位总数
     * @throws Exception SQL执行异常
     */
    @Override
    public Integer getAvailableCount() throws Exception {
        String sql = "SELECT COUNT(*) FROM parking_seat WHERE seat_state = 0";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            if (rs.next()) {
                return rs.getInt(1);
            }
        }
        return 0;
    }

    /**
     * 查询固定车位和VIP车位
     * @return 固定车位和VIP车位列表
     * @throws Exception SQL执行异常
     */
    @Override
    public List<ParkingSeat> getFixedAndVip() throws Exception {
        String sql = "SELECT * FROM parking_seat WHERE (seat_tag = '固定车位' OR seat_tag = 'VIP车位') AND seat_state = 0 ORDER BY seat_num";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            List<ParkingSeat> list = new ArrayList<>();
            while (rs.next()) {
                list.add(extractParkingSeat(rs));
            }
            return list;
             }
    }

    /**
     * 根据车位号查询车位号
     * @param seatNum 车位号
     * @return 车位号
     * @throws Exception SQL执行异常
     */
    @Override
    public String getSeatNum(String seatNum) throws Exception {
        String sql = "SELECT seat_num FROM parking_seat WHERE seat_num = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, seatNum);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getString("seat_num");
                }
            }
        }
        return "";
    }

    /**
     * 从ResultSet中提取ParkingSeat对象的辅助方法
     * @param rs ResultSet结果集
     * @return ParkingSeat对象
     * @throws Exception SQL执行异常
     */
    private ParkingSeat extractParkingSeat(ResultSet rs) throws SQLException {
        ParkingSeat seat = new ParkingSeat();
        seat.setSeatId(rs.getString("seat_id"));
        seat.setSeatNum(rs.getString("seat_num"));
        seat.setSeatSection(rs.getString("seat_section"));
        seat.setSeatState(rs.getInt("seat_state"));
        seat.setSeatTag(rs.getString("seat_tag"));
        seat.setUserId(rs.getInt("user_id"));
        seat.setCarNumber(rs.getString("car_number"));
        return seat;
    }
}